package com.itmk.classes.dao.impl;

import com.itmk.classes.dao.ClassesDao;
import com.itmk.classes.entity.ClassEditVo;
import com.itmk.classes.entity.ClassParm;
import com.itmk.classes.entity.Classes;
import com.itmk.common.BaseDao;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang.StringUtils;

import java.sql.SQLException;
import java.util.List;

public class ClassesDaoImpl extends BaseDao implements ClassesDao {
    @Override
    public void add(Classes classes) {
        //1、定义sql
        String sql = "insert into classes(calss_id,major_id,calss_name,order_num) values(?,?,?,?)";
        //2、定义handler
        BeanHandler<Classes> handler = new BeanHandler<>(Classes.class);
        //3、组装参数
        Object[] parms = {
                classes.getCalssId(),
                classes.getMajorId(),
                classes.getCalssName(),
                classes.getOrderNum()
        };
        try {
            query.insert(sql,handler,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Classes> list(ClassParm parm) {
        //判断是否有关键字查询
        if(StringUtils.isNotEmpty(parm.getClassName())){
            //1、定义sql
            StringBuilder sb = new StringBuilder("select * from (select classes.* ,ROWNUM rm from classes order by order_num desc )");
            sb.append(" where calss_name like '%" + parm.getClassName() + "%' ");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Classes> handler = new BeanListHandler<>(Classes.class,processor);
            try {
                return query.query(sql,handler);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
        }else {
            StringBuilder sb = new StringBuilder("select * from (select classes.* ,ROWNUM rm from classes order by order_num desc )");
            sb.append(" where rm >= ? and rm <= ?");
            String sql = sb.toString();
            //2、定义handler
            BeanListHandler<Classes> handler = new BeanListHandler<>(Classes.class,processor);
            //3、构造参数
            Object[] parms = {
                    (parm.getPage()-1)*parm.getLimit(),
                    (parm.getPage()*parm.getLimit())
            };
            try {
                return query.query(sql,handler,parms);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            return null;
        }

    }

    @Override
    public int getCount(ClassParm parm) {
         //1、定义sql
        StringBuilder sb = new StringBuilder("select count(calss_id) from classes");
        //判断是否有关键字查询
        if(StringUtils.isNotEmpty(parm.getClassName())){
            sb.append(" where calss_name like '%" + parm.getClassName() + "%'");
        }
        String sql = sb.toString();
        //2、定义handler
        ScalarHandler handler = new ScalarHandler();

        try {
            return Integer.valueOf(query.query(sql,handler).toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public ClassEditVo getClassByClassId(String classId) {
        //1、定义sql
        String sql = "select cs.*,cg.colloge_id from classes cs,major mj, colloge cg where  cs.major_id =mj.major_id and mj.colloge_id = cg.colloge_id and calss_id = ? ";
        //2、定义handler
        BeanHandler<ClassEditVo> handler = new BeanHandler<>(ClassEditVo.class,processor);

        try {
            return query.query(sql,handler,classId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
    }
        return null;
    }

    @Override
    public void update(Classes classes) {
        //1、定义sql
        String sql = "update classes set major_id=?,calss_name=?,order_num=? where calss_id=?";
        //2、定义参数
        Object[] parms = {
                classes.getMajorId(),
                classes.getCalssName(),
                classes.getOrderNum(),
                classes.getCalssId()
        };
        try {
            query.update(sql,parms);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public void delete(String claasId) {
        //1、定义sql
        String sql = "delete from classes where  calss_id=?";
        try {
            query.update(sql,claasId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    @Override
    public List<Classes> getListforStudent() {
        //1、定义sql
        String sql = "select * from classes";
        //2、定义handler
        BeanListHandler<Classes> handler = new BeanListHandler<>(Classes.class,processor);
        try {
           return query.query(sql,handler);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
